package org.dizem.minichat.util;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.dizem.minichat.Constants;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
 * User: DIZEM
 * Time: 11-3-2 下午4:37
 */
public class DBUtil {


	/**
	 * Use sql to do select query and get results
	 * the results will be putted into a list
	 *
	 * @param cls   class type of the result
	 * @param query sql query string
	 * @return a list of objects get from database by the query
	 * @author dizem
	 */
	public static Object getDataList(Class<?> cls, String query) {
		DbUtils.loadDriver(Constants.DB_DRIVER);
		Connection c = null;
		try {
			c = DriverManager.getConnection(Constants.DB_URL);
			return new QueryRunner().query(c, query, new BeanListHandler(cls));

		} catch (SQLException e) {
			e.printStackTrace();

		} finally {
			DbUtils.closeQuietly(c);
		}
		return null;
	}


	/**
	 * Use sql to do select query and get one result
	 * The '?' in the query string will be replaced by params
	 * e.g. query = "select * from s where id=? and name=?"
	 * params = ["1000", "dizem"]
	 *
	 * @param cls	class type of result
	 * @param query  query string
	 * @param params a list of query to replace '?' in the query string
	 * @return the only object get from database
	 * @author dizem
	 */
	public static Object getData(Class<?> cls, String query, Object... params) {
		DbUtils.loadDriver(Constants.DB_DRIVER);
		Connection c = null;
		try {
			c = DriverManager.getConnection(Constants.DB_URL);
			return new QueryRunner().query(c, makeQueryString(query, params), new BeanHandler(cls));

		} catch (SQLException e) {
			e.printStackTrace();

		} finally {
			DbUtils.closeQuietly(c);
		}
		return null;
	}

	private static String makeQueryString(String sql, Object... param) {
		for (Object item : param) {
			if (item instanceof Integer)
				sql = sql.replaceFirst("\\?", item.toString());
			else
				sql = sql.replaceFirst("\\?", "'" + item.toString() + "'");
		}
		System.out.println(sql);
		return sql;
	}

	/**
	 * Use query to do update(update, delete, insert, ..) operation
	 * The '?' in the query string will be replaced by params
	 * e.g. query = "select * from s where id=? and name=?"
	 * params = ["1000", "dizem"]
	 *
	 * @param query  query string
	 * @param params a list of query to replace '?' in the query string
	 * @return number of lines updated successfully
	 * @author dizem
	 */
	public static int update(String query, Object... params) {
		DbUtils.loadDriver(Constants.DB_DRIVER);
		Connection c = null;
		try {
			c = DriverManager.getConnection(Constants.DB_URL);
			return new QueryRunner().update(c, makeQueryString(query, params));

		} catch (SQLException e) {
			e.printStackTrace();

		} finally {
			DbUtils.closeQuietly(c);
		}
		return 0;
	}
}
